SQL DROP COLUMN and ADD COLUMN: adding and removing columns in SQL

In this article, we’ll take a look at how to safely add and drop columns from a SQL database using ALTER TABLE … ADD and ALTER TABLE … DROP COLUMN.

We will also briefly touch on adding and dropping constraints on SQL tables, since that functions in the same way and since it’s important to consider constraints anyway when you’re adding columns to a table.

But first, a quick note…

Does this work in MySQL, PostgreSQL, SQlite, SQL Server, Oracle, etc.?

While there are a variety of SQL “flavors” for relational databases, adding and dropping columns is generally handled the same way in each of them. We’re using CockroachDB syntax in this article, but the same commands will generally work in MySQL, SQlite, Oracle, and other SQL databases.

One thing to watch out for, though, is data types. While relational databases all tend to support most of the same types of data, they aren’t always called the same thing, and they don’t always function in precisely the same way. For example, in this article we’ll be adding a column using the STRING datatype in CockroachDB, but in some other relational databases this would be called VARCHAR, and in some cases you might have to define a length limit in bytes or characters. Refer to the documentation for your specific database software to ensure you’re using the correct data type for your use case.

Our example table

Let’s start with an example table, so that we can walk through the process of adding a column, adding a constraint, dropping a constraint, and then dropping the column.

We’ll start with this very simple table, called users:

That doesn’t include much information, so let’s add a column to make it a little more useful.

If you’d like to follow along with this article in your own database, here’s a quick SQL statement to copy-paste that will create the above table (though you may need to adjust the data types depending on your RDBMS):

CREATE TABLE users (
    id INT PRIMARY KEY,
    first_name STRING
);

INSERT INTO users (id, first_name) VALUES (1, 'ellen'), (2, 'parker'), (3,'ash');

How to add a column with ALTER TABLE … ADD COLUMN

The basic syntax pattern for adding a column is:

ALTER TABLE table_name ADD COLUMN column_name datatype constraint

In the above command, we need to replace all of the lower-case elements with the specifics of the column we want to add. (Note that the word COLUMN is optional in many RDBMS; ALTER TABLE table_name ADD column_name will also work.)

Our simple users table is missing an email column, so let’s add one. When adding a column, we always want to consider constraints, and in this case, we should ensure that:

1. Every email in the column is unique, so that we can’t have multiple accounts tied to the same email address.
2. The email column is not null, so that we can’t have accounts without an email address linked.

Here’s the “add column” command with all of the above constraints added:

ALTER TABLE users ADD COLUMN email STRING UNIQUE NOT NULL;

That will add a new column to our users table that’s called email. Entries in this column must be of the data type STRING, they must be unique in the column, and they cannot be null.

However, running this command with our existing table won’t work. That’s because we’re trying to add a column to the table without adding values to it, but our constraints say that values in this column can’t be null.

There are a variety of ways we can work around this, but we’ll take a simple step-by-step approach here so that we can also demonstrate how to add constraints.

Step 1: Add the column without constraints

To do this, we can simply run the above command without the constraints, like so:

ALTER TABLE users ADD COLUMN email STRING;

Running this command will result in the following table:

So far, so good!

Step 2: Insert values into the empty column

Again, there are other ways to do this, but to keep things simple and clear we’ll use UPDATE statements to update each row in sequence:

UPDATE users SET email = 'ellen@weyland.corp' WHERE id = 1;
UPDATE users SET email = 'parker@yutani.corp' WHERE id = 2;
UPDATE users SET email = 'ash@hyperdyne.corp' WHERE id = 3;

This results in the following table:

That looks perfect, but we haven’t added our UNIQUE and NOT NULL constraints yet. In CockroachDB we can quickly verify this using the SHOW CONSTRAINTS command:

SHOW CONSTRAINTS FROM users;

  table_name | constraint_name | constraint_type |       details        | validated
-------------+-----------------+-----------------+----------------------+------------
  users     | users_pkey     | PRIMARY KEY     | PRIMARY KEY (id ASC) |     t
(1 row)

As we can see, the only constraint on this table right now is the primary key. Let’s add our constraints to the email column so that we can ensure any rows added to this table can’t have missing or duplicate email addresses.

Step 3: Add constraints to a column

Constraints are added to columns differently, depending on the specific constraint we’re adding. In this case, we’ll need to use two slightly different commands to add our UNIQUE and NOT NULL constraints. (Again, we’re using CockroachDB/PostgreSQL syntax here, your RDBMS may handle this slightly differently).

First, let’s add the UNIQUE constraint, which we can do with ALTER TABLE … ADD CONSTRAINT:

ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);

Note that in the above command, we’re also giving the constraint a descriptive name, email_unique so that we can easily tell what it is when looking at a list of constraints.

Next, we’ll add the NOT NULL constraint using ALTER TABLE … ALTER COLUMN:

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

We’ve successfully added a column, some data, and constraints to that column to our SQL table.

Step 4: (Optional) Check that the constraints were added successfully

We can try to insert some bad data to see that our constraints are working as intended. If we try to insert a fourth row with a NULL value in the email column…

INSERT INTO users (id, first_name, email) VALUES (4, 'lambert', NULL);

… we can see that our NOT NULL constraint prevents this row from being added to the table, as it should:

ERROR: null value in column "email" violates not-null constraint
SQLSTATE: 23502

If we try to insert a fourth row that includes an email that already exists in the table…

INSERT INTO users2 (id, first_name, email) VALUES (4, 'lambert', 'ash@hyperdyne.corp');

…again, we can see that the database is rejecting it, as expected due to the UNIQUE constraint we added.

ERROR: duplicate key value violates unique constraint "email_unique"
SQLSTATE: 23505

So, we’ve successfully added a column. Now, how do we do the opposite?

How to drop a column with ALTER TABLE … DROP COLUMN

To drop a column from a table, we will use a very similar-looking command to the one we used to add a table. The basic syntax looks like this:

ALTER TABLE table_name DROP COLUMN column_name;

However, dropping a column means removing data, which is always dangerous. And although we’re only working with a single table here, in a real-world database a column may be indexed, and it might be referenced by other tables via foreign keys, so we need to approach dropping columns cautiously.

With dropping columns in particular, it’s important check the documentation for your specific RDBMS and version, as different databases handle it differently and have different security measures in place.

In CockroachDB if we try to drop a column, by default we’ll get an error:

ALTER TABLE users DROP COLUMN first_name;

ERROR: rejected (sql_safe_updates = true): ALTER TABLE DROP COLUMN will remove all data in that column.

This is a security feature, designed to ensure we don’t accidentally delete data we didn’t intend to. To remove the column, we’ll need to first turn sql_safe_updates off:

SET sql_safe_updates = false;

Now we can drop the column:

ALTER TABLE users DROP COLUMN first_name;

And we can see that this worked by viewing our table again:

Dropping a column that is indexed

If the column you intend to drop is part of one or more indexes, it can still be dropped using the same commands described above.

However, it’s important to be aware that dropping an indexed column will also drop all of the indexes that used that column.

Dropping columns with dependent objects (views, foreign key references)

We sometimes have columns in a table that are referenced in another table via foreign keys, or that are referenced by other SQL objects like views.

If we want to drop a column only if it has no dependent objects, in CockroachDB we can add RESTRICT to specify that the column should only be dropped if it has no dependent objects:

ALTER TABLE users DROP COLUMN first_name RESTRICT;

Alternatively, we might want to drop a column and its dependent objects. To do this, we can add CASCADE to ensure that dependent objects get dropped along with the column. Needless to say, this command can delete a lot of data, so it’s important to be sure you know what those dependencies are before you run it!

ALTER TABLE users DROP COLUMN first_name CASCADE;

Dropping constraints from a column

Again, there is slightly different syntax we need to use depending on the constraint we’d like to drop. And again, there are some specifics that will vary based on your RDBMS.

In regular PostgreSQL, to drop the UNIQUE constraint we added to the table, we can use ALTER TABLE … DROP CONSTRAINT like so:

ALTER TABLE users DROP CONSTRAINT email_unique;

Most relational databases will handle dropping a UNIQUE constraint similarly. In CockroachDB specifically though, dropping UNIQUE constraints is handled differently. We can remove the constraint by removing the index that was automatically created when it was added, like so:

DROP INDEX email_unique;

What about our NOT NULL constraint?

Just as we did when adding it, we’ll need to use ALTER TABLE … ALTER COLUMN to drop the NOT NULL constraint:

ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

Again, we can confirm this worked by adding new data and checking the table. For example, if we add a fourth row to our table with a null email field after running the above command to drop the NOT NULL constraint, we’ll get this:

(Recall that we removed the first_name column in an earlier step; that is why it is missing here).

Next steps

Feeling like you’ve mastered adding and dropping columns and constraints from SQL tables? A great next step is to make your relational database, well, relational by using foreign keys to add data relationships between tables. And don’t forget to watch out for common foreign key mistakes!